from openpyxl import load_workbook
import time
 
# 加载两个Excel文件
wb1 = load_workbook('D:/wwwroot/Python/tool/files/玉岩实验亲情电话-收款明细表.xlsx')
wb2 = load_workbook('D:/wwwroot/Python/tool/files/玉岩实验学校-家长管理.xlsx')
 
# 选择工作表
sheet1 = wb1.active
sheet2 = wb2.active
 
rowIndex = 2
# 比对两个表格，这里以第一列作为关键字比对
for row1 in sheet1.iter_rows(min_row=2):  # 假设第一行是标题行，从第二行开始
    studentName = row1[4].value
    #gradeName = row1[5].value
    #className = row1[6].value
    phone = row1[7].value

    result = 0
    resultNum = 0
    remarks = ''
    updateGradeName = ''
    updateclassName = ''

    if studentName == None :
        break


    for row2 in sheet2.iter_rows(min_row=2):

        studentName2 = row2[0].value
        gradeName2 = str(row2[2].value)
        className2 = str(row2[3].value)
        phone2 = row2[6].value

        if studentName2 == None :
            break

        if studentName == studentName2:  # 假设比对第一列
            # 如果找到匹配项，根据需求修改sheet1中的数据
            # 例如，将sheet1的第二列和sheet2的第三列数据进行更新
            #sheet1.cell(row=rowIndex, column=12).value = 1
            #sheet.cell(row=2, column=2).value = '另一个新值'
            result = 1
            resultNum = resultNum +1
            remarks = remarks+ "|"+gradeName2+'-'+className2
            updateGradeName = gradeName2
            updateclassName = className2

        #else:
            #sheet1.cell(row=rowIndex, column=12).value = 0

    if result == 1 :
        # 写入匹配结果
        sheet1.cell(row=rowIndex, column=12).value = updateGradeName
        sheet1.cell(row=rowIndex, column=13).value = updateclassName
        sheet1.cell(row=rowIndex, column=14).value = resultNum
        sheet1.cell(row=rowIndex, column=15).value = result
        sheet1.cell(row=rowIndex, column=16).value = remarks
    rowIndex = rowIndex +1        
 
# 保存修改后的文件
fileName = str(time.time())
wb1.save('D:/wwwroot/Python/tool/files/'+fileName+'.xlsx')